## Report Library v1.0
## David Ray - January 21, 2011
## Standard functions for Reporting
## BodyOfEmail -> returns a body including the passed message and a nice footer.
## SendEmail -> sends the report email
## Set-ExcelVars -> creates a set of variables useful with Excel objects
## Open-ExcelCSV -> Opens up a csv file in Excel
## Save-ExcelCSV -> Saves an Excel file as xlsx or xlsb
## Set-ExcelHeaderFormat -> Pretties up the top of the Excel file some
## Set-ExcelAutofitColumns -> Autofits all Excel columns


Function Set-ExcelVars
{
	## The constants that we will use

	$global:xlCenter = -4108
	$global:xlCellValue = 1
	$global:xl3TrafficLights2 = 5
	$global:xl3Symbols = 7
	$global:xlConditionValueNumber = 0
	$global:xlIconSet = 6
	$global:xlAutomatic = -4105
	$global:xlBottom = -4107
	$global:xlContinuous = 1
	$global:xlThin = 2
	$global:xlBetween = 1
	$global:xlNotBetween = 2
	$global:xlEqual = 3
	$global:xlNotEqual = 4
	$global:xlGreater = 5
	$global:xlLess = 6
	$global:xlGreaterEqual = 7
	$global:xlLessEqual = 8
	$global:xlOpenXMLWorkbook = 51
	$global:xlToRight = -4161
	$global:xlExcel12 = 50
	$global:xlTimePeriod = 11
	$global:xlYesterday = 1
	$global:xlTomorrow = 6
	$global:xlToday = 0
	$global:xlThisWeek = 3
	$global:xlThisMonth = 9
	$global:xlNextWeek = 7
	$global:xlNextMonth = 8
	$global:xlLastWeek = 4
	$global:xlLastMonth = 5
	$global:xlLast7Days = 2
	$global:xlExpression = 2
}	

Function Open-ExcelCSV ([string] $reportPath, [string] $reportName)
{
# Create an instance of Excel
Write-Debug "Opening Report Path: $($reportPath) \ $($reportName)"
$global:Excel = New-Object -comobject Excel.Application
$global:Excel.visible = $True 
$global:bucket = $Excel.Workbooks.Open("$($reportPath)\$($reportName).csv")  #$bucket is simply a variable to catch unwanted output so it doesn't go to screen (bit-bucket)
$global:Worksheet = $Excel.Worksheets.Item(1)
$global:Worksheet.Name = $reportName
}


Function Save-Excel ([string] $reportPath, [string] $reportName, [int] $format)
{
Write-Debug "Writing Report Path: $($reportPath) \ $($reportName) with format $($format)"
switch ($format)
	{
		$xlOpenXMLWorkbook {$global:reportFullName = "$($ReportPath)\$($ReportName).xlsx" }
		$xlExcel12	{$global:reportFullName = "$($ReportPath)\$($ReportName).xlsb" }
		default { $global:reportFullName = "$($ReportPath)\$($ReportName).xlsx"; $format = $xlOpenXMLWorkbook }
	}
Write-Debug "Report full name: $($reportFullName)"
if (Test-Path $reportFullName) {Remove-Item $reportFullName}
$global:Excel.DisplayAlerts = $false
$global:Excel.Workbooks.Item(1).SaveAs($reportFullName,$format)
$global:Excel.Quit()
$global:Worksheet = $null
$global:Excel = $null
[GC]::Collect()
#$loopCount=0
#while ((Get-Process "excel" -ErrorAction SilentlyContinue) -ne $null)
#	{
#		Write-Debug "Waiting 5 seconds for Excel to close - loop $($loopcount)" 
#		Start-Sleep -Seconds 5
#		$loopCount += 1
#	}
}

Function Set-ExcelHeaderFormat 
{
# Add a little formatting to the column header
$Range = $global:Worksheet.Range("A1",$global:Worksheet.Range("A1").End($global:xlToRight))
$Range.Interior.ColorIndex = 19
$Range.Font.ColorIndex = 11
$Range.Font.Bold = $True
$Range.Borders.LineStyle = $global:xlContinuous
$Range.Borders.ColorIndex = 0
$Range.Borders.TintAndShade = 0
$Range.Borders.Weight = $global:xlThin
}

Function Set-ExcelAutofitColumns
{
$global:bucket = $global:Worksheet.Range("A:Z").entireColumn.Autofit()
}

Function Send-Email ([string] $OneOffEmailAddr, [array] $defaultTo, [array] $defaultCc, [array] $defaultBcc, [string] $defaultFrom, `
					[string] $subject, [string] $emailbody, [string] $attachmentFile, [string] $SMTPserver)
{
	
	if ($OneOffEmailAddr -eq "default") {
		$to = $defaultTo
		$cc = $defaultCc
		$bcc = $defaultBcc
		}
	else {
		$to = $OneOffEmailAddr
		$cc = ""
		$bcc = ""
	}

	$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
	$msg = new-object Net.Mail.MailMessage
	$msg.From = new-object System.Net.Mail.MailAddress($defaultFrom)
	$msg.Subject = $subject
	$msg.Body = BodyOfEmail($emailbody)
	$to | foreach {Write-Debug "$_"; $msg.To.Add($_)}
	if ($cc.Length -gt 0) {$cc | ForEach-Object {Write-Debug "$_"; $msg.cc.Add($_)}}
	if ($bcc.Length -gt 0) {$bcc | ForEach-Object {$msg.bcc.Add($_)}}
	$attachment = new-object Net.Mail.Attachment($attachmentFile)
	$msg.attachments.add($attachment)
	$msg.IsBodyHtml = $true

	$mailer.send($msg)
	
	#$mailer = $null
	#$msg = $null
	Write-Debug "About to Dispose Attachment"
	$attachment.dispose()
	
}

Function BodyOfEmail ([String] $Message="") {
$Body = @"
<html>
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=Generator content="Microsoft Word 14 (filtered)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin-top:0in;
	margin-right:0in;
	margin-bottom:10.0pt;
	margin-left:0in;
	line-height:115%;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
	{mso-style-link:"Balloon Text Char";
	margin:0in;
	margin-bottom:.0001pt;
	font-size:8.0pt;
	font-family:"Tahoma","sans-serif";}
span.MsoPlaceholderText
	{color:gray;}
span.BalloonTextChar
	{mso-style-name:"Balloon Text Char";
	mso-style-link:"Balloon Text";
	font-family:"Tahoma","sans-serif";}
.MsoChpDefault
	{font-family:"Calibri","sans-serif";}
.MsoPapDefault
	{margin-bottom:10.0pt;
	line-height:115%;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=WordSection1>
<p class=MsoNormal>CHW Messaging Reporting</p>
<p class=MsoNormal>$($Message)</p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal><span style='font-size:9.0pt;line-height:115%;color:#BFBFBF'>Scripted
in Powershell by <a href="mailto:david_f_ray@dell.com"><span style='color:#0000BF'>David
Ray</span></a> {$($ScriptName)}</span></p>
<p class=MsoNormal>&nbsp;</p>
</div>
</body>
</html>
"@
return $Body
}

function Confirm-ExchangeSnapIn {
#Requires -pssnapin Microsoft.Exchange.Management.PowerShell.Admin
$exchangeSnapinFound = $False 
Get-PSSnapin | 
	ForEach { 
		If ($_.Name -EQ 'Microsoft.Exchange.Management.PowerShell.Admin') { 
			$exchangeSnapinFound = $True
		} 
	}
If (-Not $exchangeSnapinFound) { 
	Add-PSSnapin 'Microsoft.Exchange.Management.PowerShell.Admin' -ErrorAction 'Stop'
} 
}



#---------------8<---------------Function Time-Zone
Function Get-TimeZoneName {
	$timeZone = get-WmiObject -Query 'Select * From Win32_TimeZone'
	if ($timeZone.DaylightInEffect) {
		$timeZone.DaylightName
	}else{
		$timeZone.StandardName
	}
} #end of Function Get-TimeZoneName

